﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Net.Mail;

public partial class JobDetail : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Convert.ToBoolean(Session["sAdministrator"].ToString()) == false)
            {
                this.btnStartJob.Enabled = false;
                this.btnClearHistory.Visible = false;
                this.BtnMSDB_OK.Visible = false;
            }
            BindData();
        }
    }

    private void BindData()
    {
        SqlConnection cn = new SqlConnection(@"server=" + Request.QueryString["InstanceName"].ToString() + ";database=msdb;Integrated Security=SSPI;Pooling=False");
        cn.Open();
        
        SqlDataAdapter ad1 = new SqlDataAdapter(@"SELECT j.job_id,j.[name] as jobname,s.last_outcome_message,j.enabled, j.[description] as jobdescription, j.date_created, j.date_modified FROM msdb..sysjobs j LEFT JOIN sysjobservers s on j.job_id = s.job_id WHERE j.job_id = '" + Request.QueryString["JobID"].ToString()+"'", cn);
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1, "data1");
        DataRow dr1 = ds1.Tables[0].Rows[0];

        this.lnkInstanceDetail.Text = Request.QueryString["InstanceName"].ToString();
        this.lnkInstanceDetail.NavigateUrl = "~/InstanceDetail.aspx?InstanceName=" + Request.QueryString["InstanceName"].ToString();
        this.lblJobName.Text = dr1["jobname"].ToString();
        this.lblJobID.Text = dr1["job_id"].ToString();
        this.lblJobEnabled.Text = dr1["enabled"].ToString();
        this.lblJobCreated.Text = dr1["date_created"].ToString();
        this.lblJobModified.Text = dr1["date_modified"].ToString();
        this.lblJobDescription.Text = dr1["jobdescription"].ToString();

        this.lblLastRunStatus.Text = dr1["last_outcome_message"].ToString();
    
        //Add in the job steps for the jobstep dropdown list
        SqlDataAdapter ad2 = new SqlDataAdapter(@"SELECT CONVERT(VARCHAR(25),step_id)+' - '+step_name as 'step_id', step_name FROM sysjobsteps WHERE job_id = '" + dr1["job_id"].ToString() + "' ORDER BY step_id", cn);
        DataSet ds2 = new DataSet();
        ad2.Fill(ds2, "data2");
 
        this.ddlStep.DataSource = ds2;
        this.ddlStep.DataTextField = "step_id";
        this.ddlStep.DataValueField = "step_name";
        this.ddlStep.DataBind();

        SqlDataAdapter ad = new SqlDataAdapter(@"SELECT step_name,CASE run_status When 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' When 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In-Progress' WHEN 5 THEN 'Unknown' END AS 'run_status',SUBSTRING(CAST(run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(run_date AS CHAR(8)),2) + '/' + LEFT(CAST(run_date AS CHAR(8)),4)+' '+LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) AS 'LastRunDate', LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) AS 'duration' FROM sysjobhistory sjh WHERE job_id = '" + dr1["job_id"].ToString() + "' and step_id = 0 ORDER BY LastRunDate DESC", cn);
        DataSet ds = new DataSet();
        ad.Fill(ds, "data");

        this.gv.DataSource = ds;
        this.gv.DataBind();
        
        cn.Close();

        this.btnFilter.Text = "View Steps";
    }
    protected void btnStartJob_Click(object sender, EventArgs e)
    {
        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        SqlConnection cn = new SqlConnection(@"server=" + this.lnkInstanceDetail.Text.ToString() + ";database=msdb;Integrated Security=SSPI;Pooling=False");
        cn.Open();

        //MSDBOKAY the Job
        SqlCommand cmdSQL1 = cn.CreateCommand();
        cmdSQL1.CommandText = @"UPDATE sysjobservers SET last_run_outcome = 1 WHERE job_id = '" + this.lblJobID.Text.ToString() + "'";
        cmdSQL1.CommandTimeout = 150;
        cmdSQL1.ExecuteNonQuery();

        //Restart the job.
        SqlCommand cmdSQL = cn.CreateCommand();
        cmdSQL.CommandText = @"sp_start_job @job_name='" + this.lblJobName.Text.Replace("'", "''").ToString() + "', @step_name='" + this.ddlStep.SelectedValue.ToString() + "'";
        cmdSQL.CommandTimeout = 150;
        cmdSQL.ExecuteNonQuery();

        MailMessage msg = new MailMessage();

        msg.To.Add(Utility.DBANotificationEMailString());
        msg.Subject = "DBA Alert: SQL Job Re-Started";
        msg.Body = "-----------------------------------------------------" + " \r\n" +
                   "       Alert : SQL Job Re-Started" + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   " SQL Instance: " + this.lnkInstanceDetail.Text.ToString() + " \r\n" +
                   "      SQL Job: " + this.lblJobName.Text.ToString() + " \r\n" +
                   " SQL Job Step: " + this.ddlStep.SelectedValue.ToString() + " \r\n" +
                   " " + " \r\n" +
                   "  Actioned By: " + Session["sFullUserName"].ToString() + " \r\n" +
                   " " + " \r\n" +
                   "--------------------------------------------------------- " + " \r\n" +
                   " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                   "--------------------------------------------------------- " + " \r\n" +
                   "END OF ALERT";

        SmtpClient client = new SmtpClient();
        client.Send(msg);

        cn.Close();

        //Delete the record from the repository
        DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM FailedScheduledTasks WHERE InstanceName = '" + this.lnkInstanceDetail.Text.ToString() + "' AND JobName = '" + this.lblJobName.Text.Replace("'", "''").ToString() + "'");
        db.ExecuteNonQuery(dbCommand);

        Response.Redirect("~/Dashboard.aspx");
    }
    protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string run_status = Convert.ToString(DataBinder.Eval(e.Row.DataItem,"run_status"));

            if (run_status == "Failed")
            {
                e.Row.BackColor = System.Drawing.Color.Red;
            }                
        }
    }
    protected void btnFilter_Click(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection(@"server=" + Request.QueryString["InstanceName"].ToString() + ";database=msdb;Integrated Security=SSPI;Pooling=False");
        cn.Open();

        if (this.btnFilter.Text == "Hide Steps")
        {
            //Steps are visible so hide them
            SqlDataAdapter ad = new SqlDataAdapter(@"SELECT step_name,CASE run_status When 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' When 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In-Progress' WHEN 5 THEN 'Unknown' END AS 'run_status',SUBSTRING(CAST(run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(run_date AS CHAR(8)),2) + '/' + LEFT(CAST(run_date AS CHAR(8)),4)+' '+LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) AS 'LastRunDate', LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) AS 'duration' FROM sysjobhistory sjh WHERE job_id = '" + this.lblJobID.Text.ToString() + "' and step_id = 0", cn);
            DataSet ds = new DataSet();
            ad.Fill(ds, "data");

            this.gv.DataSource = ds;
            this.gv.DataBind();

            this.btnFilter.Text = "View Steps";
        }
        else
        {
            //Steps are not visible go get them
            SqlDataAdapter ad = new SqlDataAdapter(@"SELECT step_name,CASE run_status When 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' When 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In-Progress' WHEN 5 THEN 'Unknown' END AS 'run_status',SUBSTRING(CAST(run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(run_date AS CHAR(8)),2) + '/' + LEFT(CAST(run_date AS CHAR(8)),4)+' '+LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) AS 'LastRunDate', LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) AS 'duration' FROM sysjobhistory sjh WHERE job_id = '" + this.lblJobID.Text.ToString() + "'", cn);
            DataSet ds = new DataSet();
            ad.Fill(ds, "data");

            this.gv.DataSource = ds;
            this.gv.DataBind();

            this.btnFilter.Text = "Hide Steps";
        }

        cn.Close();
    }
    protected void btnClearHistory_Click(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection(@"server=" + Request.QueryString["InstanceName"].ToString() + ";database=msdb;Integrated Security=SSPI;Pooling=False");
        cn.Open();

        SqlCommand cmdSQL = cn.CreateCommand();
        
        cmdSQL.CommandText = @"DELETE FROM sysjobhistory WHERE job_id = '" + this.lblJobID.Text.ToString() + "'";
        cmdSQL.CommandTimeout = 150;
        cmdSQL.ExecuteNonQuery();

        SqlCommand cmdSQL1 = cn.CreateCommand();

        cmdSQL1.CommandText = @"UPDATE sysjobservers SET last_run_outcome = 1 WHERE job_id = '" + this.lblJobID.Text.ToString() + "'";
        cmdSQL1.CommandTimeout = 150;
        cmdSQL1.ExecuteNonQuery();

        cn.Close();

        //Delete the record from the repository
        DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM FailedScheduledTasks WHERE InstanceName = '" + this.lnkInstanceDetail.Text.ToString() + "' AND JobName = '" + this.lblJobName.Text.Replace("'", "''").ToString() + "'");
        db.ExecuteNonQuery(dbCommand);

        Response.Redirect("~/Dashboard.aspx");

    }
    protected void BtnMSDB_OK_Click(object sender, EventArgs e)
    {        
        SqlConnection cn = new SqlConnection(@"server=" + Request.QueryString["InstanceName"].ToString() + ";database=msdb;Integrated Security=SSPI;Pooling=False");
        cn.Open();

        SqlCommand cmdSQL = cn.CreateCommand();

        cmdSQL.CommandText = @"UPDATE sysjobservers SET last_run_outcome = 1 WHERE job_id = '" + this.lblJobID.Text.ToString() + "'";
        cmdSQL.CommandTimeout = 150;
        cmdSQL.ExecuteNonQuery();

        cn.Close();

        //Delete the record from the repository
        DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM FailedScheduledTasks WHERE InstanceName = '"+this.lnkInstanceDetail.Text.ToString()+"' AND JobName = '"+this.lblJobName.Text.Replace("'", "''").ToString()+"'");
        db.ExecuteNonQuery(dbCommand);

        Response.Redirect("~/Dashboard.aspx");
    }
}
